import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
import org.fusesource.jansi.AnsiConsole;

public class ConsultaSaidas {

	public static void cosaidas() {
		AnsiConsole.out.print("\u001b[2J");
		Scanner dados = new Scanner(System.in);
		SimpleDateFormat dataconver = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat datamascara = new SimpleDateFormat("dd/MM/yyyy");
		short x=1, seleciona;
		boolean erro;
		long bolsa=0;
		Date dataatual = new Date();
		Date datasaida, dde, dat;
		String data="", bolsaid="";
		String databanco = dataconver.format(dataatual);
		String cpf="", cnpj="", datade="", dataate="";
		System.out.println("___________________________________Hemouna______________________________________");
		System.out.println("______________________________Consulta - Saidas_________________________________");
		System.out.println("\n\n\n");
		try{
			Class.forName("org.hsqldb.jdbcDriver" ); 	    
			Connection con = DriverManager.getConnection("jdbc:hsqldb:file:Banco/BD", "sa", "");
			Statement stm = con.createStatement();			
			while (x == 1){
				System.out.println(" Deseja pesquisar por");
				System.out.println("	1 - Para paciente...");
				System.out.println("	2 - Para hospital... ");
				System.out.println("	3 - Numero da bolsa...");
				System.out.println("	4 - Por data...");
				System.out.println("	0 - Voltar");
				seleciona = dados.nextShort();
				System.out.println("\n");
				switch(seleciona){
					case 1:{
						System.out.print("CPF (Somente Numeros): ");
						erro = true;
						while (erro == true){
							cpf = dados.next();
							if (cpf.length() == 11){
								erro = false;
							} else {
								System.out.println("CPF invalido. Informe um valido.");
							}
						}
						ResultSet rs = stm.executeQuery("SELECT * FROM BOLSADESANGUE_HAS_PACIENTE where paciente_cpf="+cpf+" order by datasaida");
						System.out.printf("+-------------+----------------+------------+                                   ");
						System.out.printf("|    CPF      |    N. Bolsa    |    Saida   |                                   ");
						System.out.printf("+-------------+----------------+------------+                                   ");
						while(rs.next()){
					        bolsaid = rs.getString("tipobolsadesangue_id");
					        datasaida = rs.getDate("datasaida");
					        data = datamascara.format(datasaida);
					        System.out.printf("| "+Espaco.add(cpf, 11)+"| "+Espaco.add(bolsaid, 14)+"| "+Espaco.add(data, 10)+"|                                   ");  
					    }
						System.out.printf("+-------------+----------------+------------+                                   \n\n");
						break;
					}
					case 2:{
						System.out.print("CNPJ (Somente Numeros): ");
						erro = true;
						while (erro == true){
							cnpj = dados.next();
							if (cnpj.length() == 14){
								erro = false;
							} else {
								System.out.println("CNPJ invalido. Informe um valido.");
							}
						}
						ResultSet rs = stm.executeQuery("SELECT * FROM BOLSADESANGUE_HAS_HOSPITAL where hospital_cnpj="+cnpj+" order by datasaida");
						System.out.printf("+----------------+----------------+------------+                                ");
						System.out.printf("|      CNPJ      |    N. Bolsa    |    Saida   |                                ");
						System.out.printf("+----------------+----------------+------------+                                ");
						while(rs.next()){
					        bolsaid = rs.getString("tipobolsadesangue_id");
					        datasaida = rs.getDate("datasaida");
					        data = datamascara.format(datasaida);
					        System.out.printf("| "+Espaco.add(cnpj, 14)+"| "+Espaco.add(bolsaid, 14)+"| "+Espaco.add(data, 10)+"|                                ");  
					    }
						System.out.printf("+----------------+----------------+------------+                                \n\n");
						break;
					}
					case 3:{
						System.out.print("Numero da bolsa: ");
						erro=true;
						while (erro == true){
							try {
								bolsa = dados.nextLong();
								erro = false;
							} catch (Exception e) {
								System.out.print("Informe somente numeros\n");
								dados.nextLine();
							}
						}
						System.out.printf("+-------------------------------------------+                                   ");
						System.out.printf("|                  Paciente                 |                                   ");
						System.out.printf("+-------------+----------------+------------+                                   ");
						System.out.printf("|    CPF      |    N. Bolsa    |    Saida   |                                   ");
						System.out.printf("+-------------+----------------+------------+                                   ");
						ResultSet rs = stm.executeQuery("SELECT * FROM BOLSADESANGUE_HAS_PACIENTE where tipobolsadesangue_id="+bolsa+" order by datasaida");
						while(rs.next()){
							cpf = rs.getString("paciente_cpf");
					        bolsaid = rs.getString("tipobolsadesangue_id");
					        datasaida = rs.getDate("datasaida");
					        data = datamascara.format(datasaida);
					        System.out.printf("| "+Espaco.add(cpf, 11)+"| "+Espaco.add(bolsaid, 14)+"| "+Espaco.add(data, 10)+"|                                   ");  
						}
						System.out.printf("+-------------+----------------+------------+                                    \n\n");
						System.out.printf("+----------------------------------------------+                                ");
						System.out.printf("|                     Hospital                 |                                ");
						System.out.printf("+----------------+----------------+------------+                                ");
						System.out.printf("|      CNPJ      |    N. Bolsa    |    Saida   |                                ");
						System.out.printf("+----------------+----------------+------------+                                ");
						ResultSet rs1 = stm.executeQuery("SELECT * FROM BOLSADESANGUE_HAS_HOSPITAL where tipobolsadesangue_id="+bolsa+" order by datasaida");
						while(rs1.next()){
							cnpj = rs1.getString("paciente_hospital_cnpj");
					        bolsaid = rs1.getString("tipobolsadesangue_id");
					        datasaida = rs1.getDate("datasaida");
					        data = datamascara.format(datasaida);
					        System.out.printf("| "+Espaco.add(cnpj, 14)+"| "+Espaco.add(bolsaid, 14)+"| "+Espaco.add(data, 10)+"|                                ");  
						}
						System.out.printf("+----------------+----------------+------------+                                \n\n");
						break;
					}
					case 4:{
						dados.nextLine();
						System.out.print("De (dd/mm/aaaa): ");
						datade = dados.nextLine();
						System.out.print("Ate (dd/mm/aaaa): ");
						dataate = dados.nextLine();
						dde = datamascara.parse(datade);
						dat = datamascara.parse(dataate);
						String dataatebanco = dataconver.format(dat);
						String datadebanco = dataconver.format(dde);
						System.out.printf("+----------------+----------------+------------+                                ");
						System.out.printf("|    CPF/CNPJ    |    N. Bolsa    |    Saida   |                                ");
						System.out.printf("+----------------+----------------+------------+                                ");
						ResultSet rs = stm.executeQuery("SELECT * FROM BOLSADESANGUE_HAS_PACIENTE where datasaida >= '"+datadebanco+"' and datasaida <= '"+dataatebanco+"' order by datasaida");
						while(rs.next()){
							cpf = rs.getString("paciente_cpf");
					        bolsaid = rs.getString("tipobolsadesangue_id");
					        datasaida = rs.getDate("datasaida");
					        data = datamascara.format(datasaida);
					        System.out.printf("| "+Espaco.add(cpf, 14)+"| "+Espaco.add(bolsaid, 14)+"| "+Espaco.add(data, 10)+"|                                ");  
					    }
						ResultSet rs1 = stm.executeQuery("SELECT * FROM BOLSADESANGUE_HAS_HOSPITAL where datasaida >= '"+datadebanco+"' and datasaida <= '"+dataatebanco+"' order by datasaida");
						while(rs1.next()){
							cnpj = rs1.getString("hospital_cnpj");
					        bolsaid = rs1.getString("tipobolsadesangue_id");
					        datasaida = rs1.getDate("datasaida");
					        data = datamascara.format(datasaida);
					        System.out.printf("| "+Espaco.add(cnpj, 14)+"| "+Espaco.add(bolsaid, 14)+"| "+Espaco.add(data, 10)+"|                                ");  
					    }
						System.out.printf("+----------------+----------------+------------+                                \n\n");
						break;
					}
					case 0:{
						MenuConsulta.meconsulta();
					}
					
					default: System.out.println("Opcao invalida.");
				}				
				
				System.out.println("--------------------------------------------------------------------------------");
				System.out.println("Fazer outra pesquisa? 1-Sim ou 2-Nao");
				x = dados.nextShort();
			}
			stm.execute("SHUTDOWN");
		}catch(ClassNotFoundException e){
		    System.out.println("Erro ao carregar o driver JDBC. ");
		}catch(Exception e){
		    System.out.println("Erro de SQL: "+e);
		    e.printStackTrace();
		}
		MenuConsulta.meconsulta();

	}

}
